#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2018-06-09 16:08:16
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.6.5
# 功能     ：

import pyodbc
import os
import re
import time_ext_
from queue import Queue
import logging
import threading


class PoolConnect(object):
    """docstring for Connect"""
    pool=None

    def __init__(self, arg):
        super(PoolConnect, self).__init__()
        self.arg = arg
        self.log = logging.getLogger("main.access")
        self.setupDeviceDsn()
        self.maxConn = self.getMaxConn()
        self.createConn()
        self.check_connect()
        self.th=threading.Thread(target=self.check_thread)
        self.th.setDaemon(True)
        self.th.start()
    # 初始队列

    def getMaxConn(self):
        if not int(self.arg['maxconnections']):
            return Queue()
        else:
            return Queue(int(self.arg['maxconnections']))
    # 初始创建连接

    def createConn(self):
        try:
            if int(self.arg['mincached']) >= 1:
                for i in range(int(self.arg['mincached'])):
                    conn = self.create()
                    self.maxConn.put(conn)
                self.log.info("初始化连接数：%s" % self.maxConn.qsize())
        except Exception as e:
            self.log.error("初始化连接数失败：%s" % e)
    # 回收连接

    def closeConn(self, conn):
        if not conn:
            return
        if self.maxConn.qsize() < int(self.arg['maxconnections']):
            self.maxConn.put(conn)
        elif self.maxConn.qsize() == 0:
            self.createConn()
        else:
            del conn
    # 安装access2010 ODBC 驱动

    def setupDeviceDsn(self):
        dbPath = self.arg['database']
        exp = re.findall('\.(\w+)', os.path.split(dbPath)[1])[0]
        userinfo = 'Dbq=%s;Pwd=%s;' % (dbPath, self.arg['password'])
        if exp.lower() == 'accdb':
            if 'Microsoft Access Driver (*.mdb, *.accdb)' not in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                self.log.warning('本机没有安装ACCESS2010以上数据库驱动，即将安装驱动')
                path = os.path.abspath(
                    './setup/access2010/AccessDatabaseEngine.exe')
                self.log.info("后台安装Access2010 ODBC驱动程序，请稍候。。。")
                os.system(path + ' /quiet')
                while 'Microsoft Access Driver (*.mdb, *.accdb)' not in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                    self.log.info("正在安装。。。")
                    time_ext_.sleep(5)
                else:
                    self.log.info("安装Access2010 ODBC驱动成功")
    # 创建连接

    def create(self):
        dbPath = self.arg['database']
        exp = re.findall('\.(\w+)', os.path.split(dbPath)[1])[0]
        userinfo = 'Dbq=%s;Pwd=%s;' % (dbPath, self.arg['password'])
        if exp.lower() == 'accdb':
            if 'Microsoft Access Driver (*.mdb, *.accdb)'in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                return pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};' + userinfo)
            else:
                self.log.info("No DSN")
        else:
            return pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};' + userinfo)

    def loadSqlDict(self, sql):
        connect=None
        try:
            connect = self.maxConn.get(timeout=1)
            cursor = connect.cursor()
            data = []
            cursor.execute(sql)
            value = cursor.fetchall()
            if value and len(value) >= 1:
                field = tuple([d[0] for d in cursor.description])
                for v in value:
                    data.append(dict(zip(field, v)))
                cursor.close()
                return data
            else:
                # self.log.info("查询数据0条2")
                return []
        except Exception as e:
            self.log.warning("查询数据:%s 错误：%s " % (sql, e))
            return -1
        finally:
            self.closeConn(connect)

    def loadSqlList(self, sql):
        try:
            connect = self.maxConn.get(timeout=1)
            cursor = connect.cursor()
            data = []
            cursor.execute(sql)
            value = cursor.fetchall()
            if value and len(value) >= 1:
                data = value
                cursor.close()
                return data
            else:
                self.log.info("查询数据0条")
                return []
        except Exception as e:
            self.log.warning("查询数据:%s 错误：%s " % (sql, e))
            return -1
        finally:
            self.closeConn(connect)

    def execSql(self, sql):
        try:
            connect = self.maxConn.get(timeout=1)
            cursor = connect.cursor()
            cursor.execute(sql)
            rec = cursor.rowcount
            connect.commit()
            #self.log.info("执行语句：%s 2"%sql)
            if rec >= 1:
                self.log.info("数据执行成功，影响%d行 " % rec)
                cursor.close()
                return rec
            else:
                self.log.info("数据执行成功，影响%d行" % rec)
                return 0
        except Exception as e:
            self.log.warning("sql:%s,执行数据失败：%s " % (sql, e))
        finally:
            self.closeConn(connect)
            return -1

    def execSqlList(self, sql, values):
        try:
            connect = self.maxConn.get(timeout=1)

            cursor = connect.cursor()
            cursor.executemany(sql, values)
            rec = abs(cursor.rowcount)
            connect.commit()
            #self.log.info("执行语句：%s 2"%sql)
            if rec >= 1:
                self.log.info("数据执行成功，影响%d行 " % rec)
                cursor.close()
                return rec
            else:
                self.log.info("数据执行成功，影响%d行" % rec)
                return 0
        except Exception as e:
            self.log.warning("执行数据失败：%s " % e)
        finally:
            self.closeConn(connect)
            return -1

    def check_thread(self):
        while True:
            self.check_connect()
            time_ext_.sleep(120)
    def check_connect(self):
            sql="SELECT NOW()"
            data=self.loadSqlDict(sql)
            if data:
                self.pool=True
            else:
                self.pool=False
                self.log.warning("检测到ACCESS 数据库连接异常")


class Connect(object):
    """docstring for Connect"""

    def __init__(self, arg):
        super(Connect, self).__init__()
        self.arg = arg
        self.log = logging.getLogger("main.access")
        self.setupDeviceDsn()
        self.conn = self.connect()
        # self.conn.close()
    # 安装access2010 ODBC 驱动

    def setupDeviceDsn(self):
        dbPath = self.arg['database']
        exp = re.findall('\.(\w+)', os.path.split(dbPath)[1])[0]
        userinfo = 'Dbq=%s;Pwd=%s;' % (dbPath, self.arg['password'])
        if exp.lower() == 'accdb':
            if 'Microsoft Access Driver (*.mdb, *.accdb)' not in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                self.log.warning('本机没有安装ACCESS2010以上数据库驱动，即将安装驱动')
                path = os.path.abspath(
                    './setup/access2010/AccessDatabaseEngine.exe')
                self.log.info("后台安装Access2010 ODBC驱动程序，请稍候。。。")
                os.system(path + ' /quiet')
                while 'Microsoft Access Driver (*.mdb, *.accdb)' not in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                    self.log.info("正在安装。。。")
                    time_ext_.sleep(5)
                else:
                    self.log.info("安装Access2010 ODBC驱动成功")

    def connect(self):
        dbPath = self.arg['database']
        exp = re.findall('\.(\w+)', os.path.split(dbPath)[1])[0]
        userinfo = 'Dbq=%s;Pwd=%s;' % (dbPath, self.arg['password'])
        if exp.lower() == 'accdb':
            if 'Microsoft Access Driver (*.mdb, *.accdb)'in [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]:
                return pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};' + userinfo)
            else:
                # self.setupAccessDriver()
                pass
        else:
            if self.arg.get('dsn'):
                driver_str = "DSN={};UID=admin;PWD={};".format(self.arg.get('dsn'), self.arg['password'] or '')
                return pyodbc.connect(driver_str, timeout = 1, autocommit = True)
            return pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};' + userinfo)

    def loadSqlDict(self, sql):
        try:
            connect = self.connect()
            cursor = connect.cursor()
            data = []
            cursor.execute(sql)
            value = cursor.fetchall()
            if value and len(value) >= 1:
                field = tuple([d[0] for d in cursor.description])
                for v in value:
                    data.append(dict(zip(field, v)))
                cursor.close()
                connect.close()
                return data
            else:
                # self.log.info("查询数据0条2")
                return []
        except Exception as e:
            self.log.warning("查询数据:%s 错误：%s " % (sql, e))
            return -1

    def loadSqlList(self, sql):
        try:
            connect = self.connect()
            cursor = connect.cursor()
            data = []
            cursor.execute(sql)
            value = cursor.fetchall()
            if value and len(value) >= 1:
                data = value
                cursor.close()
                connect.close()
                return data
            else:
                self.log.info("查询数据0条")
                return 0
        except Exception as e:
            self.log.warning("查询数据:%s 错误：%s " % (sql, e))
            return -1

    def execSql(self, sql):
        try:
            connect = self.connect()
            cursor = connect.cursor()
            cursor.execute(sql)
            rec = cursor.rowcount
            connect.commit()
            #self.log.info("执行语句：%s 2"%sql)
            if rec >= 1:
                self.log.info("数据执行成功，影响%d行 " % rec)
                cursor.close()
                connect.close()
                return rec
            else:
                self.log.info("数据执行成功，影响%d行" % rec)
                return 0
        except Exception as e:
            self.log.warning("sql:%s,执行数据失败：%s " % (sql, e))
            return -1

    def execSqlList(self, sql, values):
        try:
            connect = self.connect()
            cursor = connect.cursor()
            cursor.executemany(sql, values)
            rec = abs(cursor.rowcount)
            connect.commit()
            #self.log.info("执行语句：%s 2"%sql)
            if rec >= 1:
                self.log.info("数据执行成功，影响%d行 " % rec)
                cursor.close()
                connect.close()
                return abs(rec)
            elif rec is 0:
                self.log.info("数据执行成功，影响%d行" % rec)
                return rec

        except Exception as e:
            self.log.warning("执行数据失败：%s " % e)
            return -1


if __name__ == "__main__":
    import ReadConfig
    import log
    import time_ext_
    config = ReadConfig.Config('./config/config.ini')
    coninfo = config.read()
    logg = log.Logger(coninfo['Log'])
    connect = PoolConnect(coninfo['Access'], logg.write)

    conn = Connect(coninfo['Access'], logg.write)
    logg.write(conn.loadSqlDict("SELECT now"))
    while 1:
        logg.write(connect.loadSqlDict(" SELECT now"))
        times = int(time_ext_.time())
        YPBMS = ['2018102720-2018102725', '2018102720-2018102726',
                 '2018102720-2018102727', '2018102720-2018102728']
        values = [(times, 123456789, v[11:] or '0', times, 4) for v in YPBMS]
        sql = """
                INSERT INTO T_MISSION (F_GID,F_ID,F_ORDER,F_TYPE,F_STATUS,F_YPMM,F_UserOperation,F_DtStarPlan,F_MaxTimeOut,F_DTSTARTOP,F_DTENDOP,F_ZDSBH,F_ErrInfo )
                VALUES                ( ?,?,    1,      1,      3,     ?,          570,          ?,           0,         0,         0,       ?,      0    )
                """
        print(connect.execSqlList(sql, values))
        time_ext_.sleep(20)
